﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for SanPhamDA
/// </summary>
public class SanPhamDA
{
    public SanPham getSanPhamByID(String ID)
    {
        try
        {
            Connection con = new Connection();
            SqlCommand com = new SqlCommand("SanPham_getbyID", con.getCon());
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.Add("ID", SqlDbType.VarChar, 20).Value = ID;
            SqlDataAdapter da = new SqlDataAdapter(com);
            DataTable dt = new DataTable();
            da.Fill(dt);
            SanPham sp = new SanPham();
                sp.Sanphamid = dt.Rows[0]["MaSanPham"].ToString();
                sp.Giaban = Convert.ToInt32(dt.Rows[0]["GiaBan"].ToString());
                sp.Ngaycapnhat = Convert.ToDateTime(dt.Rows[0]["NgayCapNhat"].ToString());
                sp.Loaid = Convert.ToInt32(dt.Rows[0]["LoaiID"].ToString());
                sp.Xoa = Convert.ToBoolean(dt.Rows[0]["Xoa"].ToString());
                sp.Gioithieu = dt.Rows[0]["GioiThieu"].ToString();
                sp.Nhanhieuid = Convert.ToInt32(dt.Rows[0]["NhanHieuID"].ToString());
                //sp.Chucnangid = Convert.ToInt32(dt.Rows[0]["ChucNangID"].ToString());
            
            return sp;
        }
        catch (Exception)
        {

            throw;
        }
        finally {
            conn.closeCon();
        }
    }
    public DataTable getAll()
    {
        try
        {
            string str = "SELECT (select top 1 URL from HinhAnh where HinhAnh.MaSanPham=SanPham.MaSanPham) as URL ,SanPham.MaSanPham, SanPham.GiaBan, SanPham.NgayCapNhat, SanPham.LoaiID, SanPham.Xoa, SanPham.GioiThieu, SanPham.ChucNangID, SanPham.NhanHieuID, Loai.TenLoai, NhanHieu.Ten AS TenNhanHieu, ChucNang.Ten AS TenChucNang FROM SanPham INNER JOIN Loai ON SanPham.LoaiID = Loai.ID INNER JOIN NhanHieu ON SanPham.NhanHieuID = NhanHieu.ID LEFT OUTER JOIN ChucNang ON SanPham.ChucNangID = ChucNang.ID WHERE (SanPham.Xoa = 0 and NhanHieu.Xoa=0 and Loai.Xoa=0 and (ChucNang.Xoa=0 or SanPham.chucnangid is null)) order by SanPham.GiaBan ASC";
            SqlCommand com = new SqlCommand(str, conn.getCon());
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(com);
            da.Fill(dt);
            return dt;

        }
        catch (Exception)
        {

            throw;
        }
        finally {
            conn.closeCon();
        }
    }
    Connection conn = new Connection();
    public DataTable getDSSanPhambyHang(int hangid)
    {
        try
        {
            string str = "SELECT (select top 1 URL from HinhAnh where HinhAnh.MaSanPham=SanPham.MaSanPham) as URL ,SanPham.MaSanPham, SanPham.GiaBan, SanPham.NgayCapNhat, SanPham.LoaiID, SanPham.Xoa, SanPham.GioiThieu, SanPham.ChucNangID, SanPham.NhanHieuID, Loai.TenLoai, NhanHieu.Ten AS TenNhanHieu, ChucNang.Ten AS TenChucNang FROM SanPham INNER JOIN Loai ON SanPham.LoaiID = Loai.ID INNER JOIN NhanHieu ON SanPham.NhanHieuID = NhanHieu.ID LEFT OUTER JOIN ChucNang ON SanPham.ChucNangID = ChucNang.ID WHERE (SanPham.NhanHieuID = @ID) AND (SanPham.Xoa = 0 and NhanHieu.Xoa=0 and Loai.Xoa=0 and (ChucNang.Xoa=0 or SanPham.chucnangid is null)) order by SanPham.GiaBan ASC";
            SqlCommand com = new SqlCommand(str, conn.getCon());
            com.Parameters.Add("@ID", SqlDbType.Int).Value = hangid;
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(com);
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally {
            conn.closeCon();
        }

    }
    public DataTable getDSSanPhambyLoai(int loaiid)
    {
        try
        {
            string str = "SELECT (select top 1 URL from HinhAnh where HinhAnh.MaSanPham=SanPham.MaSanPham) as URL ,SanPham.MaSanPham, SanPham.GiaBan, SanPham.NgayCapNhat, SanPham.LoaiID, SanPham.Xoa, SanPham.GioiThieu, SanPham.ChucNangID, SanPham.NhanHieuID, Loai.TenLoai, NhanHieu.Ten AS TenNhanHieu, ChucNang.Ten AS TenChucNang FROM SanPham INNER JOIN Loai ON SanPham.LoaiID = Loai.ID INNER JOIN NhanHieu ON SanPham.NhanHieuID = NhanHieu.ID LEFT OUTER JOIN ChucNang ON SanPham.ChucNangID = ChucNang.ID WHERE (SanPham.LoaiID = @ID) AND (SanPham.Xoa = 0 and NhanHieu.Xoa=0 and Loai.Xoa=0 and (ChucNang.Xoa=0 or SanPham.chucnangid is null)) order by SanPham.GiaBan ASC";
            SqlCommand com = new SqlCommand(str, conn.getCon());
            com.Parameters.Add("@ID", SqlDbType.Int).Value = loaiid;
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(com);
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally {
            conn.closeCon();
        }

    }
    public DataTable getDSSanPhambyChucNang(int chucnangid)
    {
        try
        {
            string str = "SELECT (select top 1 URL from HinhAnh where HinhAnh.MaSanPham=SanPham.MaSanPham) as URL ,SanPham.MaSanPham, SanPham.GiaBan, SanPham.NgayCapNhat, SanPham.LoaiID, SanPham.Xoa, SanPham.GioiThieu, SanPham.ChucNangID, SanPham.NhanHieuID, Loai.TenLoai, NhanHieu.Ten AS TenNhanHieu, ChucNang.Ten AS TenChucNang FROM SanPham INNER JOIN Loai ON SanPham.LoaiID = Loai.ID INNER JOIN NhanHieu ON SanPham.NhanHieuID = NhanHieu.ID INNER JOIN ChucNang ON SanPham.ChucNangID = ChucNang.ID WHERE (SanPham.ChucNangID = @ID) AND (SanPham.Xoa = 0 and NhanHieu.Xoa=0 and Loai.Xoa=0 and (ChucNang.Xoa=0 or SanPham.chucnangid is null)) order by SanPham.GiaBan ASC";
            SqlCommand com = new SqlCommand(str, conn.getCon());
            com.Parameters.Add("@ID", SqlDbType.Int).Value = chucnangid;
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(com);
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally {
            conn.closeCon();
        }

    }
    public DataTable getDSSanPhambyHang_Loai(int hangid, int loaiid)
    {
        try
        {
            string str = "SELECT (select top 1 URL from HinhAnh where HinhAnh.MaSanPham=SanPham.MaSanPham) as URL ,SanPham.MaSanPham, SanPham.GiaBan, SanPham.NgayCapNhat, SanPham.LoaiID, SanPham.Xoa, SanPham.GioiThieu, SanPham.ChucNangID, SanPham.NhanHieuID, Loai.TenLoai, NhanHieu.Ten AS TenNhanHieu, ChucNang.Ten AS TenChucNang FROM SanPham INNER JOIN Loai ON SanPham.LoaiID = Loai.ID INNER JOIN NhanHieu ON SanPham.NhanHieuID = NhanHieu.ID LEFT OUTER JOIN ChucNang ON SanPham.ChucNangID = ChucNang.ID WHERE (SanPham.LoaiID = @loaiid) AND (SanPham.NhanHieuID=@hangid) AND (SanPham.Xoa = 0 and NhanHieu.Xoa=0 and Loai.Xoa=0 and (ChucNang.Xoa=0 or SanPham.chucnangid is null)) order by SanPham.GiaBan ASC";
            SqlCommand com = new SqlCommand(str, conn.getCon());
            com.Parameters.Add("@hangid", SqlDbType.Int).Value = hangid;
            com.Parameters.Add("@loaiid", SqlDbType.Int).Value = loaiid;
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(com);
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally {
            conn.closeCon();
        }
    }
    public DataTable getDSSanPhambychucnang_Loai(int chucnangid, int loaiid)
    {
        try
        {
            string str = "SELECT (select top 1 URL from HinhAnh where HinhAnh.MaSanPham=SanPham.MaSanPham) as URL ,SanPham.MaSanPham, SanPham.GiaBan, SanPham.NgayCapNhat, SanPham.LoaiID, SanPham.Xoa, SanPham.GioiThieu, SanPham.ChucNangID, SanPham.NhanHieuID, Loai.TenLoai, NhanHieu.Ten AS TenNhanHieu, ChucNang.Ten AS TenChucNang FROM SanPham INNER JOIN Loai ON SanPham.LoaiID = Loai.ID INNER JOIN NhanHieu ON SanPham.NhanHieuID = NhanHieu.ID LEFT OUTER JOIN ChucNang ON SanPham.ChucNangID = ChucNang.ID WHERE (SanPham.LoaiID = @loaiid) AND (SanPham.ChucNangID=@chucnangid) AND (SanPham.Xoa = 0 and NhanHieu.Xoa=0 and Loai.Xoa=0 and (ChucNang.Xoa=0 or SanPham.chucnangid is null)) order by SanPham.GiaBan ASC";
            SqlCommand com = new SqlCommand(str, conn.getCon());
            com.Parameters.Add("@chucnangid", SqlDbType.Int).Value = chucnangid;
            com.Parameters.Add("@loaiid", SqlDbType.Int).Value = loaiid;
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(com);
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally {
            conn.closeCon();
        }
    }
    public DataTable getDSSanPhambyHang_chucnang(int hangid, int chucnangid)
    {
        try
        {
            string str = "SELECT (select top 1 URL from HinhAnh where HinhAnh.MaSanPham=SanPham.MaSanPham) as URL ,SanPham.MaSanPham, SanPham.GiaBan, SanPham.NgayCapNhat, SanPham.LoaiID, SanPham.Xoa, SanPham.GioiThieu, SanPham.ChucNangID, SanPham.NhanHieuID, Loai.TenLoai, NhanHieu.Ten AS TenNhanHieu, ChucNang.Ten AS TenChucNang FROM SanPham INNER JOIN Loai ON SanPham.LoaiID = Loai.ID INNER JOIN NhanHieu ON SanPham.NhanHieuID = NhanHieu.ID LEFT OUTER JOIN ChucNang ON SanPham.ChucNangID = ChucNang.ID WHERE (SanPham.NhanHieuID = @hangid) AND (SanPham.ChucNangID=@chucnangid) AND (SanPham.Xoa = 0 and NhanHieu.Xoa=0 and Loai.Xoa=0 and (ChucNang.Xoa=0 or SanPham.chucnangid is null)) order by SanPham.GiaBan ASC";
            SqlCommand com = new SqlCommand(str, conn.getCon());
            com.Parameters.Add("@hangid", SqlDbType.Int).Value = hangid;
            com.Parameters.Add("@chucnangid", SqlDbType.Int).Value = chucnangid;
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(com);
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally {
            conn.closeCon();
        }
    }

    public DataTable getDSSanPham(int hangid, int loaiid, int chucnangid)
    {
        try
        {
            string str = "SELECT (select top 1 URL from HinhAnh where HinhAnh.MaSanPham=SanPham.MaSanPham) as URL ,SanPham.MaSanPham, SanPham.GiaBan, SanPham.NgayCapNhat, SanPham.LoaiID, SanPham.Xoa, SanPham.GioiThieu, SanPham.ChucNangID, SanPham.NhanHieuID, Loai.TenLoai, NhanHieu.Ten AS TenNhanHieu, ChucNang.Ten AS TenChucNang FROM SanPham INNER JOIN Loai ON SanPham.LoaiID = Loai.ID INNER JOIN NhanHieu ON SanPham.NhanHieuID = NhanHieu.ID LEFT OUTER JOIN ChucNang ON SanPham.ChucNangID = ChucNang.ID where SanPham.NhanHieuID=@hangid and SanPham.LoaiID=@loaiid and SanPham.ChucNangID=@chucnangid and ( SanPham.Xoa =0 and NhanHieu.Xoa=0 and Loai.Xoa=0 and (ChucNang.Xoa=0 or SanPham.chucnangid is null)) order by SanPham.GiaBan ASC";
            SqlCommand com = new SqlCommand(str, conn.getCon());
            com.Parameters.Add("@hangid", SqlDbType.Int).Value = hangid;
            com.Parameters.Add("@loaiid", SqlDbType.Int).Value = loaiid;
            com.Parameters.Add("@chucnangid", SqlDbType.Int).Value = chucnangid;
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(com);
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally {
            conn.closeCon();
        }
    }

    public DataTable Search(string sp)
    {
        try
        {
            string str = "SELECT (select top 1 URL from HinhAnh where HinhAnh.MaSanPham=SanPham.MaSanPham) as URL ,SanPham.MaSanPham, SanPham.GiaBan, SanPham.NgayCapNhat, SanPham.LoaiID, SanPham.Xoa, SanPham.GioiThieu, SanPham.ChucNangID, SanPham.NhanHieuID, Loai.TenLoai, NhanHieu.Ten AS TenNhanHieu, ChucNang.Ten AS TenChucNang FROM SanPham INNER JOIN Loai ON SanPham.LoaiID = Loai.ID INNER JOIN NhanHieu ON SanPham.NhanHieuID = NhanHieu.ID LEFT OUTER JOIN ChucNang ON SanPham.ChucNangID = ChucNang.ID where (SanPham.Xoa = 0) AND (NhanHieu.Xoa = 0) AND (Loai.Xoa = 0) AND (ChucNang.Xoa = 0) OR (SanPham.ChucNangID IS NULL) AND (SanPham.Xoa = 0) AND (NhanHieu.Xoa = 0) AND (Loai.Xoa = 0) ";
            SqlCommand com = new SqlCommand(str, conn.getCon());
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(com);
            da.Fill(dt);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {
            conn.closeCon();
        }
    }

}